﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class TransLocationMachineSetRepo
    {
        public const string m_msg_head = "TransLocationMachineSet";

        public IEnumerable<TransLocationMachineSetModels> GetTransLocationMachineSet()
        {
            List<TransLocationMachineSetModels> TransLocationMachineSets = new List<TransLocationMachineSetModels>();
            string SQLCommand = @"SELECT * FROM TBTransLocationMachineSet";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        TransLocationMachineSetModels TransLocationMachineSet = new TransLocationMachineSetModels();
                        TransLocationMachineSet.Id = (int)reader["Id"];
                        TransLocationMachineSet.Event = (string)reader["Event"];
                        TransLocationMachineSet.tmp_code_no = (string)reader["tmp_code_no"];
                        TransLocationMachineSet.tmp_LocationSetCode = (string)reader["SerialNo"];
                        TransLocationMachineSet.tmp_SerialNo = (string)reader["Machine_Name"];
                        TransLocationMachineSet.tmp_start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        TransLocationMachineSet.tmp_last_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        TransLocationMachineSet.tmp_total_meter_value = (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
                        TransLocationMachineSet.tmp_CreateBy = (string)reader["remark"];
                        TransLocationMachineSet.tmp_CreateOn = (DateTime)reader["tmp_CreateOn"];

                        TransLocationMachineSet.tmp_RentBasic = (reader["tmp_RentBasic"] != DBNull.Value) ? SF.GetDouble(reader["tmp_RentBasic"]) : 0;
                        TransLocationMachineSet.tmp_RentForUse = (reader["tmp_RentForUse"] != DBNull.Value) ? SF.GetDouble(reader["tmp_RentForUse"]) : 0;
                        TransLocationMachineSet.tmp_ConversionRate = (int)reader["tmp_ConversionRate"];
                        TransLocationMachineSet.ref_FSR_NO = (string)reader["ref_FSR_NO"];
                        TransLocationMachineSet.ref_SerialNo = (string)reader["Machine_Name"];
                        TransLocationMachineSet.ref_start_meter_no = (reader["ref_start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["ref_start_meter_no"]) : 0;
                        TransLocationMachineSet.ref_last_meter_no = (reader["ref_last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["ref_last_meter_no"]) : 0;
                        TransLocationMachineSet.ref_total_meter_value = (reader["ref_total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["ref_total_meter_value"]) : 0;
                        TransLocationMachineSet.ref_CreateBy = (string)reader["ref_CreateBy"];
                        TransLocationMachineSet.ref_CreateOn = (DateTime)reader["ref_CreateOn"];
                        TransLocationMachineSet.remarks = (string)reader["remarks"];


                        TransLocationMachineSets.Add(TransLocationMachineSet);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return TransLocationMachineSets.ToList();
        }

        public TransLocationMachineSetModels GetLocationMachineSetById(int id)
        {
            TransLocationMachineSetModels TransLocationMachineSet = new TransLocationMachineSetModels();
            string SQLCommand = @"select * from TBTransLocationMachineSet where Id=@Id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@Id", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        TransLocationMachineSet.Id = (int)reader["Id"];
                        TransLocationMachineSet.Event = (string)reader["Event"];
                        TransLocationMachineSet.tmp_code_no = (string)reader["tmp_code_no"];
                        TransLocationMachineSet.tmp_LocationSetCode = (string)reader["SerialNo"];
                        TransLocationMachineSet.tmp_SerialNo = (string)reader["Machine_Name"];
                        TransLocationMachineSet.tmp_start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        TransLocationMachineSet.tmp_last_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        TransLocationMachineSet.tmp_total_meter_value = (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
                        TransLocationMachineSet.tmp_CreateBy = (string)reader["remark"];
                        TransLocationMachineSet.tmp_CreateOn = (DateTime)reader["tmp_CreateOn"];

                        TransLocationMachineSet.tmp_RentBasic = (reader["tmp_RentBasic"] != DBNull.Value) ? SF.GetDouble(reader["tmp_RentBasic"]) : 0;
                        TransLocationMachineSet.tmp_RentForUse = (reader["tmp_RentForUse"] != DBNull.Value) ? SF.GetDouble(reader["tmp_RentForUse"]) : 0;
                        TransLocationMachineSet.tmp_ConversionRate = (int)reader["tmp_ConversionRate"];
                        TransLocationMachineSet.ref_FSR_NO = (string)reader["ref_FSR_NO"];
                        TransLocationMachineSet.ref_SerialNo = (string)reader["Machine_Name"];
                        TransLocationMachineSet.ref_start_meter_no = (reader["ref_start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["ref_start_meter_no"]) : 0;
                        TransLocationMachineSet.ref_last_meter_no = (reader["ref_last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["ref_last_meter_no"]) : 0;
                        TransLocationMachineSet.ref_total_meter_value = (reader["ref_total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["ref_total_meter_value"]) : 0;
                        TransLocationMachineSet.ref_CreateBy = (string)reader["ref_CreateBy"];
                        TransLocationMachineSet.ref_CreateOn = (DateTime)reader["ref_CreateOn"];
                        TransLocationMachineSet.remarks = (string)reader["remarks"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return TransLocationMachineSet;
        }

        public bool Create(TransLocationMachineSetModels TransLocationMachineSet)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBTransLocationMachineSet 
                                    values (@Event,@tmp_code_no,@tmp_LocationSetCode,@tmp_SerialNo,@tmp_start_meter_no,
                                            @tmp_last_meter_no,@tmp_total_meter_value,@total_meter_value,@tmp_CreateBy,
                                            GETDATE(),@tmp_RentBasic,@tmp_RentForUse,@ref_FSR_NO,@ref_SerialNo,@ref_start_meter_no,
                                            @Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Event", TransLocationMachineSet.Id);
                    db.AddParameter("@tmp_code_no", TransLocationMachineSet.tmp_code_no);
                    db.AddParameter("@tmp_LocationSetCode", TransLocationMachineSet.tmp_LocationSetCode);
                    db.AddParameter("@tmp_SerialNo", TransLocationMachineSet.tmp_SerialNo);
                    db.AddParameter("@tmp_start_meter_no", TransLocationMachineSet.tmp_start_meter_no);
                    db.AddParameter("@tmp_last_meter_no", TransLocationMachineSet.tmp_last_meter_no);
                    db.AddParameter("@tmp_total_meter_value", TransLocationMachineSet.tmp_total_meter_value);
                    db.AddParameter("@tmp_CreateBy", TransLocationMachineSet.tmp_CreateBy);
                   // db.AddParameter("@tmp_CreateOn", TransLocationMachineSet.tmp_CreateOn);
                    db.AddParameter("@tmp_RentBasic", TransLocationMachineSet.tmp_RentBasic);

                    db.AddParameter("@tmp_RentForUse", TransLocationMachineSet.tmp_RentForUse);
                    db.AddParameter("@tmp_ConversionRate", TransLocationMachineSet.tmp_ConversionRate);
                    
                    db.AddParameter("@ref_FSR_NO", TransLocationMachineSet.ref_FSR_NO);
                    db.AddParameter("@ref_SerialNo", TransLocationMachineSet.ref_SerialNo);
                    db.AddParameter("@ref_start_meter_no", TransLocationMachineSet.ref_start_meter_no);
                    db.AddParameter("@ref_last_meter_no", TransLocationMachineSet.ref_last_meter_no);
                    db.AddParameter("@ref_total_meter_value", TransLocationMachineSet.ref_total_meter_value);
                    db.AddParameter("@ref_CreateBy", TransLocationMachineSet.ref_CreateBy);
                    db.AddParameter("@ref_CreateOn", TransLocationMachineSet.ref_CreateOn);
                    db.AddParameter("@remarks", TransLocationMachineSet.remarks);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    // var return_num =    db.ExecuteScalar(SQLCommand, DbConnectionState.KeepOpen);

                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }
        public IEnumerable<TransLocationMachineSetModels> GetTransLocationMachineSetByLocation(string LocationSetCode)
        {
            List<TransLocationMachineSetModels> TransLocationMachineSets = new List<TransLocationMachineSetModels>();
            string SQLCommand = @"Select  * From TBTransLocationMachineSet   Where tmp_LocationSetCode=@tmp_LocationSetCode";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@tmp_LocationSetCode", LocationSetCode);
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        TransLocationMachineSetModels TransLocationMachineSet = new TransLocationMachineSetModels();
                        TransLocationMachineSet.Id = (int)reader["Id"];
                        TransLocationMachineSet.Event = (string)reader["Event"];
                        TransLocationMachineSet.tmp_code_no = (string)reader["tmp_code_no"];
                        TransLocationMachineSet.tmp_LocationSetCode = (string)reader["SerialNo"];
                        TransLocationMachineSet.tmp_SerialNo = (string)reader["Machine_Name"];
                        TransLocationMachineSet.tmp_start_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        TransLocationMachineSet.tmp_last_meter_no = (reader["start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["start_meter_no"]) : 0;
                        TransLocationMachineSet.tmp_total_meter_value = (reader["total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["total_meter_value"]) : 0;
                        TransLocationMachineSet.tmp_CreateBy = (string)reader["remark"];
                        TransLocationMachineSet.tmp_CreateOn = (DateTime)reader["tmp_CreateOn"];

                        TransLocationMachineSet.tmp_RentBasic = (reader["tmp_RentBasic"] != DBNull.Value) ? SF.GetDouble(reader["tmp_RentBasic"]) : 0;
                        TransLocationMachineSet.tmp_RentForUse = (reader["tmp_RentForUse"] != DBNull.Value) ? SF.GetDouble(reader["tmp_RentForUse"]) : 0;
                        TransLocationMachineSet.tmp_ConversionRate = (int)reader["tmp_ConversionRate"];
                        TransLocationMachineSet.ref_FSR_NO = (string)reader["ref_FSR_NO"];
                        TransLocationMachineSet.ref_SerialNo = (string)reader["Machine_Name"];
                        TransLocationMachineSet.ref_start_meter_no = (reader["ref_start_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["ref_start_meter_no"]) : 0;
                        TransLocationMachineSet.ref_last_meter_no = (reader["ref_last_meter_no"] != DBNull.Value) ? SF.GetDouble(reader["ref_last_meter_no"]) : 0;
                        TransLocationMachineSet.ref_total_meter_value = (reader["ref_total_meter_value"] != DBNull.Value) ? SF.GetDouble(reader["ref_total_meter_value"]) : 0;
                        TransLocationMachineSet.ref_CreateBy = (string)reader["ref_CreateBy"];
                        TransLocationMachineSet.ref_CreateOn = (DateTime)reader["ref_CreateOn"];
                        TransLocationMachineSet.remarks = (string)reader["remarks"];
                        
                        TransLocationMachineSets.Add(TransLocationMachineSet);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    
                }
            }
            return TransLocationMachineSets.ToList();
        }

  
    }
}